# -*- coding: utf-8 -*-
import sys
sys.path.append("..")
import os
import pymysql
import time
import demjson


class MysqlReaderGTags(object):
  """
  """

  #写入文件
  def insert_file(self, fileName, dataList):
    f = "d:\\data\\mysql\\%s.sql" %(fileName)
    try:
      #只需要将之前的”w"改为“a"即可，代表追加内容
      with open(f, "a" ,encoding="utf-8") as file:
        for json in dataList:
          file.write(str(json) + "\n")
    except Exception as e:
      print(e)

    return True

  #批量插入
  def insert_batch(self, connect, cursor, fileName, insertList):
    connect.ping(reconnect = True)
    insert_errors = []
    count = 0
    for insert_sql in insertList :
      try:
        ret = cursor.execute(insert_sql)
        count += ret
      except Exception as e:
        print(e)
        insert_errors.append(insert_sql)
    # 提交，不进行提交无法保存到数据库
    connect.commit()
    #connect.close()
    #将异常的sql保存下来
    if len(insert_errors) > 0 :
      self.insert_file(fileName, insert_errors)
      print("@@@插入失败数：", len(insert_errors))

    return count

  #多行插入
  def insert_many(self, connect, cursor, insert_sql, insert_data):
    connect.ping(reconnect = True)

    # 批量插入
    try:
      res = cursor.executemany(insert_sql, insert_data)
      print("res = ", res)
      # 提交，不进行提交无法保存到数据库
      connect.commit()
    except Exception as e:
      print(e)
      connect.rollback()
    #finally:
    #connect.close()

    return True

  #统计记录数
  def count_data(self, connect, cursor, query_sql):
    connect.ping(reconnect = True)
    try:
      cursor.execute(query_sql)
      queryResult = cursor.fetchone()

    except Exception as e:
      print(e)
    #finally:

    return queryResult

  #查询数据
  def query_data(self, connect, cursor, query_sql):
    connect.ping(reconnect = True)
    try:
      cursor.execute(query_sql)
      queryResult = cursor.fetchall()

    except Exception as e:
      print(e)
    #finally:

    return queryResult

  #转换数据
  def convert_data(self, queryResult):
    convertList = []
    read_errors = []
    for x in queryResult:
      #print(x)
      rowData = {}
      order_id = x[0]
      tag_code = x[1]
      rowData.setdefault('order_id', order_id)
      rowData.setdefault('tag_code', tag_code)
      #print(rowData)
      data = x[2]
      dataDict = demjson.decode(data)
      xAxisList = dataDict['xAxis']
      seriesDataList = dataDict['series'][0]['data']
      #print(len(seriesDataList))

      try:
        for i in range(len(xAxisList)):
          try:
            tagData = {}
            tag_value = xAxisList[i]
            value_count = seriesDataList[i]
            tagData.setdefault('tag_value', tag_value)
            tagData.setdefault('value_count', value_count)
            rowData.update(tagData)
            #print(tag_id, tag_value)
            fields = rowData.keys()
            values = rowData.values()
            cols = str(tuple(fields)).replace("'", "")
            sql = 'insert into dw_group_tag_data ' + cols + ' values ' + str(tuple(values))
            #print(sql)
            convertList.append(sql)
          except Exception as ex:
            print(ex, "@@@@@@@@@x = ", x)
          finally:
            pass

      except Exception as ex:
        error_msg = str(ex) + "@@@@@@@@@tag_code = " + tag_code
        read_errors.append(error_msg)

    if len(read_errors) > 0 :
      self.insert_file('read_errors_group', read_errors)

    return convertList

  #读取异常Sql
  def readErrorList(self, fileName):
    path = "d:\\data\\mysql\\%s.sql" %(fileName)
    lines = []
    try:
      size = os.path.getsize(path)
      if size > 0:
        with open(path, "r" ,encoding="utf-8") as file:
          lines = file.readlines()
    except Exception as e:
      print(e)

    return lines

if __name__ == "__main__":
  #mysql
  odsdbConn = pymysql.connect(host='172.28.56.90', user='mysql', password='egSQ7HhxajHZjvdX', port=3306, database='odsdb',charset="utf8")
  odsdbCursor = odsdbConn.cursor()
  dwConn = pymysql.connect(host='172.28.56.90', user='mysql', password='egSQ7HhxajHZjvdX', port=3306, database='dw_v2',charset="utf8")
  dwCursor = dwConn.cursor()

  #记录执行开始时间
  str_time = int(time.time())

  #实例化类
  obj = MysqlReaderGTags()
  #insertList = []

  count_sql = "select count(*) from (select distinct order_id, sTagID, data from jg_group_tags) t "
  query_sql = "select distinct order_id, sTagID, data from jg_group_tags order by order_id limit %d, %d"

  totalCount = 2283646#obj.count_data(odsdbConn, odsdbCursor, count_sql)[0]
  print("总记录数", totalCount)
  if totalCount == 0 :
    totalCount = 1;

  pageSize = 10000
  pages = int((totalCount-1)/pageSize) + 1
  row = 0
  fileName = "error_sql_group"
  for pageNum in range(pages):
    query_sql_tmp = query_sql %((pageNum * pageSize), pageSize)
    #print(query_sql_tmp)
    result = obj.query_data(odsdbConn, odsdbCursor, query_sql_tmp)
    #print("记录数：", len(result))
    convertList = obj.convert_data(result)
    convertRow = len(convertList)
    print("转换数：", convertRow)
    #break
    #row += 1
    # 批量写入数据
    if convertRow > 0 :
      rest = obj.insert_batch(dwConn, dwCursor, fileName, convertList)
      print('插入成功数：', rest)
    #if row > 0 and (row % 100 == 0 or row == maxRow):

    #break
  errorList = obj.readErrorList(fileName)
  if len(errorList) > 0 :
    rest = obj.insert_batch(dwConn, dwCursor, "error_sql_group2", errorList)
    print('插入成功数：', rest)

  odsdbConn.close()
  dwConn.close()

  end_time = int(time.time())
  print("程序执行时间（秒）：", end_time - str_time)
